﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using DimensionsHelper.Common.Contracts.TableFormatter;
using DimensionsHelper.Common.Contracts.TableFormatter.Options;
using DimensionsHelper.Common.Contracts.Excel;
using DimensionsHelper.Common.Contracts.Excel.Options;
using DimensionsHelper.Common.Contracts.Services;
using DimensionsHelper.Common.Contracts.Table;
using DimensionsHelper.Common.TableFormatter;
using DimensionsHelper.Common.Utils;
using DimensionsHelper.Services.CsvReader;

namespace DimensionsHelper.Services.TableFormatter;

public class TableFormatter : ITableFormatter
{
    private readonly ITableReader _reader;

    private readonly List<SigComparisonResult> _tableSigResults = [];
    private string _currentSection;
    private string _replacementsPattern;

    private int _row;
    private TableMenuBuilder? _menuBuilder;
    private IExcelWorkbookOperator? _workbook;
    private IExcelWorksheetOperator? _worksheet;

    private IExcelWorkbookOperator Workbook => _workbook!;

    private IExcelWorksheetOperator Worksheet => _worksheet!;
        

    public string Section => "CsvFormatter";


    public TableFormatter(TableFormatterOptions options, ITableReader reader)
    {
        Settings = options;
        _reader = reader;
        _workbook = null;
        _menuBuilder = null;
        _row = 1;
        _currentSection = string.Empty;
        _replacementsPattern = string.Empty;
        BuildReplacementsPattern();
    }


    public TableFormatter(ITableReader reader) : this(new TableFormatterOptions(), reader)
    {
    }


    public TableFormatter() : this(new CsvStreamReader())
    {
    }
        

    public IFilePikerService? FilePikerService { get; set; }
        
    public IReportService? ReportService { get; set; }
        
    public TableFormatterOptions Settings { get; private set; }
    
    public bool LoadSettings(TableFormatterOptions options)
    {
        Settings = options;
        BuildReplacementsPattern();

        // 合并全局配置

        MergeRangeStyle(options.Table.BaseLabelStyleOptions, TableGlobalOptionFlags.BaseLabel);
        MergeRangeStyle(options.Table.BaseValueStyleOptions, TableGlobalOptionFlags.BaseValue);
        MergeRangeStyle(options.Table.DataStyleOptions, TableGlobalOptionFlags.Data);
        MergeRangeStyle(options.Table.HeaderStyleOptions, TableGlobalOptionFlags.Header);
        MergeRangeStyle(options.Table.InnerTextStyleOptions, TableGlobalOptionFlags.InnerText);
        MergeRangeStyle(options.Table.SectionStyleOptions, TableGlobalOptionFlags.Section);
        MergeRangeStyle(options.Table.SectionMemberStyleOptions, TableGlobalOptionFlags.SectionMember);
        MergeRangeStyle(options.Table.TitleStyleOptions, TableGlobalOptionFlags.Title);
        MergeRangeStyle(options.Table.SideStyleOptions, TableGlobalOptionFlags.Side);

        return true;
    }


    public bool LoadSettings(object obj)
    {
        if (obj is TableFormatterOptions options)
        {
            return LoadSettings(options);
        }

        return false;
    }


    public bool LoadSettings(string jsonFile)
    {
        try
        {
            var jsonOptions = JsonHelper.ReadJson(jsonFile, ServiceJsonContext.Default.TableFormatterOptions);
                        
            if (jsonOptions != null)
            {
                Settings = jsonOptions;
                BuildReplacementsPattern();
                return true;
            }

            ReportService?.Error($"配置文件'{jsonFile}'转换配置对象时失败。");
        }
        catch (Exception e)
        {
            ReportService?.Error(e.Message);
        }

        return false;
    }


    public void Execute(IExcelWorkbookOperator destination, string inputFileName)
    {
        var success = true;

        try
        {
            PrepareWorkbook(destination);
            ReadTextFile(inputFileName);
            StartWorksheet(inputFileName);
            FillWorksheet();
        }
        catch (Exception ex)
        {
            ReportService?.Error(ex.Message);
            success = false;
        }
        finally
        {
            Finish(destination.FileName, success);
        }
    }


    public void Execute(IExcelWorkbookOperator destination, IEnumerable<string> inputFileNames)
    {
        Execute(destination, inputFileNames, CancellationToken.None);
    }


    public void Execute(IExcelWorkbookOperator destination, IEnumerable<TableInputFileInfo> inputFiles,
        CancellationToken cancellationToken)
    {
        var success = true;

        try
        {
            PrepareWorkbook(destination);

            foreach (var file in inputFiles)
            {
                if (cancellationToken.IsCancellationRequested)
                {
                    success = false;
                    break;
                }

                ReadTextFile(file.FileName);

                // 插入文件
                foreach (var item in file.InsertFiles)
                {
                    var insertLine = item.LineNumber;
                    var insertTableName = item.TableName;

                    if (insertLine == null && string.IsNullOrEmpty(insertTableName))
                    {
                        continue;
                    }

                    InsertTextFile(item.FileName, insertTableName!, insertLine);

                    ReportService?.Log(TableFormatterMessages.InsertTablesAtPosition,
                        file.FileName,
                        item.FileName,
                        insertLine != null ? insertLine.ToString() : insertTableName!,
                        _reader.TablesCount.ToString());
                }
                
                // 替换文件

                StartWorksheet(file.FileName);
                FillWorksheet(cancellationToken);
            }
        }
        catch (Exception ex)
        {
            ReportService?.Error(ex.Message);
            success = false;
        }
        finally
        {
            Finish(destination.FileName, success);
        }
    }


    public void Execute(IExcelWorkbookOperator destination, IEnumerable<string> inputFileNames,
        CancellationToken cancellationToken)
    {
        var success = true;

        try
        {
            PrepareWorkbook(destination);
            foreach (var file in inputFileNames)
            {
                if (cancellationToken.IsCancellationRequested)
                {
                    success = false;
                    break;
                }

                ReadTextFile(file);
                StartWorksheet(file);
                FillWorksheet(cancellationToken);
            }
        }
#if !DEBUG
        catch (Exception ex)
        {
            ReportService?.Error(ex.Message);
            success = false;
        }
#endif
        finally
        {
            Finish(destination.FileName, success);
        }
    }


    private void BuildReplacementsPattern()
    {
        _replacementsPattern =
            StringHelper.MakeRegExpr(Settings.TextReplacements.Keys, StringHelper.RegexpType.Normal);
    }


    private void MergeFontStyle(FontStyleOptions higherStyleOptions, TableGlobalOptionFlags flag)
    {
        if ((Settings.Table.GlobalFontOptionFlag & flag) == 0)
        {
            return;
        }

        if (string.IsNullOrEmpty(higherStyleOptions.FontFamily))
        {
            higherStyleOptions.FontFamily = Settings.Table.Font.FontFamily;
        }

        if (higherStyleOptions.FontSize <= 0)
        {
            higherStyleOptions.FontSize = Settings.Table.Font.FontSize;
        }
    }


    private void MergeRangeStyle(RangeStyleOptions higherStyleOptions, TableGlobalOptionFlags flag)
    {
        MergeFontStyle(higherStyleOptions.Font, flag);

        if ((Settings.Table.GlobalBorderOptionFlag & flag) == 0)
        {
            return;
        }

        if (higherStyleOptions.BorderStyle == BorderStyle.None)
        {
            higherStyleOptions.BorderStyle = Settings.Table.BorderStyle;
        }

        if (higherStyleOptions.BorderPosition == BorderPosition.None)
        {
            higherStyleOptions.BorderPosition = Settings.Table.BorderPosition;
        }
        
        // NumberFormat
        if (string.IsNullOrWhiteSpace(higherStyleOptions.NumberFormatLocal) ||
            higherStyleOptions.NumberFormatLocal.Equals("general", StringComparison.OrdinalIgnoreCase))
        {
            higherStyleOptions.NumberFormatLocal = Settings.Table.NumberFormatLocal;
        }
    }


    private void ReadTextFile(string fileName)
    {
        _reader.Clear();
        ReportService?.Log(TableFormatterMessages.StartReadingTextFile, fileName);
        using var fileStream = File.OpenRead(fileName);
        _reader.ReadStream(fileStream);
        _reader.BuildTables(CsvTableRecognizer.Create(Settings.Table.TitleFeature, Settings.Table.EndFeature,
            Settings.Table.BaseFeature));
        _reader.FinishStream();
        ReportService?.Log(TableFormatterMessages.FinishReadingTextFile, fileName, _reader.LinesCount.ToString(),
            _reader.TablesCount.ToString());
    }

        
    private void InsertTextFile(string fileName, string tableName, int? position)
    {
        if (string.IsNullOrEmpty(tableName) && position == null)
        {
            return;
        }

        ReportService?.Log(TableFormatterMessages.StartReadingTextFile, fileName);
        using var fileStream = File.OpenRead(fileName);
        if (!string.IsNullOrEmpty(tableName))
        {
            _reader.InsertTablesBefore(
                CsvTableRecognizer.Create(Settings.Table.TitleFeature, Settings.Table.EndFeature,
                    Settings.Table.BaseFeature),
                fileStream, tableName);
        }
        else if (position != null)
        {
            _reader.InsertTablesAt(
                CsvTableRecognizer.Create(Settings.Table.TitleFeature, Settings.Table.EndFeature,
                    Settings.Table.BaseFeature),
                fileStream, (int)position);
        }
    }


    private void PrepareWorkbook(IExcelWorkbookOperator workbookOperator)
    {
        _workbook = workbookOperator;
        _workbook.HyperlinkStyleOptions = Settings.Table.HyperlinkStyleOptions;
        _workbook.GlobalFontStyleOptions = Settings.Table.Font;

        if (!Settings.Menu.BuildMenu)
        {
            return;
        }

        _menuBuilder = new TableMenuBuilder(_workbook, Settings);
        ReportService?.Log(TableFormatterMessages.InitializeMenuWorksheet);
    }


    private void StartWorksheet(string inputFileName)
    {
        var sheetName = Path.GetFileNameWithoutExtension(inputFileName);
        ReportService?.Log(TableFormatterMessages.StartFormatingWorksheet, sheetName);
        _worksheet = Workbook.GetWorksheet(sheetName);
        _menuBuilder?.StartWorksheet(_worksheet);
        // 重排表格
        ReorderTables();
    }

    private static void ExecuteMarkBorder(IExcelRangeOperator range, SigComparisonResult result)
    {
        var flags = result.Comparison.BorderMarkConflictFlags;

        if (flags == SigComparisonBorderMarkConflictFlags.Default)
        {
            flags = SigComparisonBorderMarkConflictFlags.Top | SigComparisonBorderMarkConflictFlags.Right;
        }

        //           +--------+
        //           |  Top   |
        //           |  Cell  |
        //  +--------+--------+--------+
        //  |  Left  |  Mark  |  Right |
        //  |  Cell  |  Cell  |  Cell  |
        //  +--------+--------+--------+
        //           | Bottom |
        //           |  Cell  |
        //           +--------+
        // 
        //  添加MarkCell的边框时，根据现有配置里的SigComparisonBorderMarkConflictFlags类型的值，
        //  检查MarkCell的左、上、右、下方的四个单元格边框，如果有单元格边框已经存在，依据Flags值保留相应位置的边框。

        // 检查左侧边框
        if (range.StartColumn > 1)
        {
            CheckAndSetBorder(range.Offset(0, -1), ExcelBorderLocation.Right,
                SigComparisonBorderMarkConflictFlags.Left, ExcelBorderLocation.Left);
        }
        else
        {
            range.SetRangeBorderColor(ExcelBorderLocation.Left, BorderStyle.Thick, result.Color);
        }

        // 检查上方边框
        if (range.StartRow > 1)
        {
            CheckAndSetBorder(range.Offset(-1, 0), ExcelBorderLocation.Bottom,
                SigComparisonBorderMarkConflictFlags.Top, ExcelBorderLocation.Top);
        }
        else
        {
            range.SetRangeBorderColor(ExcelBorderLocation.Top, BorderStyle.Thick, result.Color);
        }

        // 检查右侧边框
        CheckAndSetBorder(range.Offset(0, 1), ExcelBorderLocation.Left,
            SigComparisonBorderMarkConflictFlags.Right, ExcelBorderLocation.Right);

        // 检查下方边框
        CheckAndSetBorder(range.Offset(1, 0), ExcelBorderLocation.Top,
            SigComparisonBorderMarkConflictFlags.Bottom, ExcelBorderLocation.Bottom);
        return;

        void CheckAndSetBorder(IExcelRangeOperator checkCell, ExcelBorderLocation checkBorderLocation,
            SigComparisonBorderMarkConflictFlags locationFlag, ExcelBorderLocation setBorderLocation)
        {
            var state = checkCell.GetRangeBorderState(checkBorderLocation);

            if ((flags & locationFlag) <= 0 && state.BorderStyle != BorderStyle.None && !state.IsColorEmpty() &&
                (state.IsSameColor(result.Color) || state.IsSameColor(result.AnotherColor)))
            {
                return;
            }

            checkCell.SetRangeBorderColor(checkBorderLocation, BorderStyle.Thick, result.Color);
            range.SetRangeBorderColor(setBorderLocation, BorderStyle.Thick, result.Color);
        }
    }

    private void ExecuteTableSigResults()
    {
        foreach (var res in _tableSigResults)
        {
            var rng = Worksheet.GetRange(res.Row, res.Column);

            switch (res.MarkType)
            {
                case SigComparisonMarkType.MarkAtCharacter:
                    rng.SetRangeFontStyle(new FontStyleOptions { FontColor = res.Color });
                    break;

                case SigComparisonMarkType.MarkAtBorder:
                    ExecuteMarkBorder(rng, res);
                    break;

                case SigComparisonMarkType.MarkAtInterior:
                    rng.SetStyle(new RangeStyleOptions { InteriorColor = res.Color });
                    break;
            }
        }

        _tableSigResults.Clear();
    }


    private static double GetCellNumberValue(ICell cell)
    {
        return cell.Value switch
        {
            double d => d,
            int i => i,
            _ => 0,
        };
    }


    private string CheckSigGapBoundary(ICell sigCell, string sourceEscaped)
    {
        var table = sigCell.Line.Table;
        var dataLine = sigCell.Line.PreviousLine;

        if (table == null || string.IsNullOrEmpty(sourceEscaped) ||
            dataLine == null || dataLine.Count < table.Columns)
        {
            return sourceEscaped;
        }

        var thisDataCell = dataLine[sigCell.Index];
        var thisDbValue = GetCellNumberValue(thisDataCell);

        var target = new StringBuilder();

        foreach (var c in sourceEscaped)
        {
            if (!table.TryGetSignificantTestIdColumn(c, out var col))
            {
                continue;
            }

            // target cell
            var targetCell = dataLine[col];
            var targetDbValue = GetCellNumberValue(targetCell);

            var val = thisDbValue - targetDbValue;
            var bound = Settings.Sig.GapBoundary;
            var succeed = Settings.Sig.GapBoundaryType switch
            {
                SigGapBoundaryType.Greater => val > bound,
                SigGapBoundaryType.GreaterEqual => val >= bound,
                SigGapBoundaryType.Less => val < bound,
                SigGapBoundaryType.LessEqual => val <= bound,
                _ => true,
            };

            if (succeed)
            {
                target.Append(c);
            }
        }

        return target.ToString();
    }


    private void MergeSigTest(IExcelRangeOperator dataCell, ICell sigCell, List<IExcelRangeOperator> mergedCells)
    {
        if (sigCell.IsEmpty)
        {
            return;
        }

        var escaped = sigCell.Text.Replace("*", "");

        if (string.IsNullOrEmpty(escaped))
        {
            return;
        }

        // 检查两列差值
        if (Settings.Sig.GapBoundaryType != SigGapBoundaryType.None)
        {
            escaped = CheckSigGapBoundary(sigCell, escaped);

            if (string.IsNullOrEmpty(escaped))
            {
                return;
            }
        }

        // 不隐藏列显著性字母
        if (!Settings.Sig.HideSigCellCharacter)
        {
            var grouped = TableFormatterHelper.GroupSigChars(escaped);

            foreach ((string text, bool isUpper) in grouped)
            {
                var styleOptions = isUpper ? Settings.Sig.UpperCaseStyleOptions : Settings.Sig.LowerCaseStyleOptions;
                dataCell.AppendRichText(text, styleOptions);
            }
        }
        // 隐藏列显著性字母
        else if (sigCell.Line.Table != null &&
                 sigCell.Line.Table.TryGetColumnSignificantTestId(sigCell.Index, out var columnId))
        {
            var sigLine = sigCell.Line.Table.SignificantTestColumnCharacterLine;

            if (sigLine?.FirstNotEmptyCell == null)
            {
                return;
            }

            foreach (var ch in escaped)
            {
                if (!sigCell.Line.Table.TryGetSignificantTestIdColumn(ch, out var col))
                {
                    continue;
                }

                var comp = Settings.Sig.ComparisonMap.GetByColumns(columnId, ch);

                if (comp == null || comp.StartCharactersToSkip.Contains(sigLine.FirstNotEmptyCell.Text[0].ToString()))
                {
                    continue;
                }

                /*
                 *  隐藏显著性列字母时，不按照 ColumnA 和 ColumnB 的前后顺序进行区分。
                 *  只使用 MarkAt 属性标记颜色。
                 *
                 *  当前单元格列字母不会出现在显著性结果单元格文本中（即使出现，也应当忽略），
                 *  此时只检查对比列的配置。
                 *
                 *  假设：
                 *  +---------+---------+
                 *  | ColumnA | ColumnB |
                 *  +---------+---------+
                 *  |    A    |    B    |
                 *  +---------+---------+
                 *  | number  | number  |
                 *  +---------+---------+
                 *  |    B    |         |
                 *  +---------+---------+
                 *
                 *  此时，分两种情况：
                 *    A. SigComparison.ColumnA = 'A', SigComparison.ColumnB = 'B'
                 *    B. SigComparison.ColumnA = 'B', SigComparison.ColumnB = 'A'
                 *  这两种情况，在配置中应当视为同一种，但是，在标注时要注意两种情况：
                 *    1. SigComparison.MarkAt = 'A'，颜色标记在A列
                 *    2. SigComparison.MarkAt = 'B'，颜色标记在B列
                 *
                 */

                int markColumn;
                ExcelStyleColor markColor;
                ExcelStyleColor anotherColor;

                // 标记在当前单元格所在列
                if (comp.MarkColumn == columnId)
                {
                    markColumn = sigCell.Index;
                    markColor = comp.CharAtMarkColumnColor;
                    anotherColor = comp.CharAtAnotherColumnColor;
                }
                // 其他情况，标记在另一列
                else
                {
                    markColumn = col;
                    markColor = comp.CharAtAnotherColumnColor;
                    anotherColor = comp.CharAtMarkColumnColor;
                }

                var res = new SigComparisonResult
                {
                    Row = dataCell.StartRow,
                    Column = markColumn + 1,
                    MarkType = comp.MarkType,
                    Color = markColor,
                    AnotherColor = anotherColor,
                    Comparison = comp
                };

                _tableSigResults.Add(res);
                mergedCells.Add(Worksheet.GetRange(dataCell.StartRow, markColumn + 1));
            }
        }
    }

    private string MatchCallback(Match match)
    {
        return Settings.TextReplacements.TryGetValue(match.Value, out var final) ? final : match.Value;
    }

    private object GetFinalText(object value)
    {
        if (value is string text)
        {
            return string.IsNullOrEmpty(_replacementsPattern)
                ? text
                : Regex.Replace(text, _replacementsPattern, MatchCallback);
        }

        return value;
    }

    private bool CheckRowValid()
    {
        if (ExcelHelper.IsRowValid(_row))
        {
            return true;
        }

        ReportService?.Error(TableFormatterMessages.TooManyRows);
        return false;
    }

    private bool CheckColumnValid(int col)
    {
        if (ExcelHelper.IsColumnValid(col))
        {
            return true;
        }

        ReportService?.Error(TableFormatterMessages.TooManyColumns);
        return false;
    }

    private bool WriteTableLine(ILine line)
    {
        if (!CheckRowValid())
        {
            return false;
        }

        if (line.IsEmpty)
        {
            return true;
        }

        if (!CheckColumnValid(line.Count))
        {
            return false;
        }

        line.RTrim();
        Worksheet.GetRange(_row, 1, _row, line.Count).Value = line.SelectValues(GetFinalText);
        return true;
    }

    private void WriteTableSigLine(ILine line, List<IExcelRangeOperator> mergedCells)
    {
        for (var i = 0; i < line.Count; i++)
        {
            var cell = line[i];
            var col = i + 1;

            if (!CheckColumnValid(col))
            {
                return;
            }

            var rng = Worksheet.GetRange(_row, col);
            MergeSigTest(rng, cell, mergedCells);
        }
    }

    private bool StartTable(ITable table, out int index, out string sectionText)
    {
        index = 0;
        sectionText = null!;

        if (table.Count == 0)
        {
            return false;
        }

        var text = table.TitleCell.Text;
        var startPos = Settings.Table.TitleFeature.Length;

        if (Settings.Table.RemoveTitleFeature &&
            table.TitleCell.Length >= Settings.Table.TitleFeature.Length)
        {
            startPos = 0;
            text = text[Settings.Table.TitleFeature.Length..];
            table.TitleCell.Value = text;
        }

        if (Settings.Table.ReadSection &&
            TableFormatterHelper.TryGetSectionDefinitionInText(ref text, startPos, Settings.Table.SectionFeature,
                out sectionText))
        {
            _currentSection = sectionText;
            table.TitleCell.Value = text;
        }

        if (table[0].TestLineType(LineType.BeforeHeader))
        {
            while (index < table.Count)
            {
                var line = table[index];

                if (!line.TestLineType(LineType.BeforeHeader))
                {
                    break;
                }

                if (!Settings.Table.RemoveHeaderSpec)
                {
                    if (!WriteTableLine(line))
                    {
                        return false;
                    }

                    _row++;
                }

                index++;
            }
        }

        if (string.IsNullOrEmpty(sectionText) || !Settings.Table.ShowSection)
        {
            return true;
        }

        var sectionTextCell = Worksheet.GetRange(_row, 1);
        sectionTextCell.Value =
            Settings.Table.SectionFeature.Left + sectionText + Settings.Table.SectionFeature.Right;
        sectionTextCell.SetStyle(Settings.Table.SectionStyleOptions);
        _row++;

        return true;
    }

    private bool FillHeader(ITable table, ref int index)
    {
        var headerStartRow = _row;
        var sigMarkRow = 0;

        while (index < table.Count)
        {
            var line = table[index];

            if (!line.TestLineType(LineType.Header))
            {
                break;
            }

            if (line.TestLineType(LineType.SigMark))
            {
                if (Settings.Sig.HideSigColumnMark)
                {
                    index++;
                    continue;
                }

                sigMarkRow = _row;
            }

            if (line.IsEmpty)
            {
                if (Settings.Table.RemoveHeaderEmptyRow)
                {
                    index++;
                    continue;
                }

                if (Settings.Table.MergeHeaderEmptyRow && table.Columns > 1)
                {
                    Worksheet.GetRange(_row, 2, _row, table.Columns).Merge();
                }
            }
            else
            {
                if (!WriteTableLine(line))
                {
                    return false;
                }

                if (Settings.Table.MergeHeader && !line.TestLineType(LineType.SigMark) && table.Count > 2 &&
                    !(line.Count == table.Columns && line.Count - line.NotEmptyCellCount == 1))
                {
                    TableFormatterHelper.MergeEmptyCells(
                        Worksheet.GetRange(_row, 2, _row, table.Columns));
                }
            }

            ++_row;
            ++index;
        }

        var headerEndRow = _row - 1;

        if (headerEndRow >= headerStartRow && table.Columns > 0)
        {
            var headerRange = Worksheet.GetRange(headerStartRow, 1, headerEndRow, table.Columns);
            headerRange.SetStyle(Settings.Table.HeaderStyleOptions);

            if (Settings.Table.MergeTopLeftCells && headerEndRow - headerStartRow > 0)
            {
                Worksheet.GetRange(headerStartRow, 1, headerEndRow, 1).Merge();
            }
        }

        if (sigMarkRow > 0)
        {
            Worksheet.GetRange(sigMarkRow, 2, sigMarkRow, table.Columns)
                .SetStyle(Settings.Sig.SigMarkRowStyleOptions);
        }

        return true;
    }

    private bool FillTitle(ITable table, string sectionText, int headerStartRow)
    {
        if (!CheckRowValid())
        {
            return false;
        }

        var titleRow = _row;
        var offset = Settings.Table.TitleOffset;
        var hasUpOffset = offset < 0;

        if (Settings.Table.ShowSection && !string.IsNullOrEmpty(_currentSection))
        {
            var sectionMemberCell = Worksheet.GetRange(_row, 1);
            sectionMemberCell.Value = _currentSection;
            sectionMemberCell.SetStyle(Settings.Table.SectionMemberStyleOptions);
            _row++;

            // 当标题向上偏移时，应当跳过SECTION位置
            if (offset < 0)
            {
                titleRow--;
                // 此时，视作已经向上偏移了一次
                offset++;
            }
            // 不偏移或者向下偏移时，标题行位于SECTION下一行
            else
            {
                titleRow++;
            }
        }

        // 偏移标题行
        while (offset != 0)
        {
            if (titleRow == headerStartRow)
            {
                break;
            }

            if (offset > 0)
            {
                titleRow++;
                offset--;
            }
            else
            {
                titleRow--;
                offset++;
            }
        }

        var titleCell = Worksheet.GetRange(titleRow, 1);
        titleCell.Value = table.TitleCell.Text;
        titleCell.SetStyle(Settings.Table.TitleStyleOptions);

        _menuBuilder?.PushTable(titleCell, table, sectionText);

        // 如果没有向上偏移，设置当前行为标题行的下一行
        if (!hasUpOffset)
        {
            _row = titleRow + 1;
        }

        return true;
    }

    private bool FillTable(ITable table, ref int index)
    {
        var tableBodyStartRow = _row;
        var emptyLinesCount = 0;

        var innerTextRows = new List<int>();
        var sigResultRows = new List<int>();

        var sigMergedCells = new List<IExcelRangeOperator>();

        var baseRows = new List<int>();
        var notEmptyRows = new List<int>();

        while (index < table.Count)
        {
            var line = table[index];

            if (line.TestLineType(LineType.AfterBody))
            {
                break;
            }

            if (!line.IsEmpty)
            {
                emptyLinesCount = 0;
            }
            else if (!line.TestLineType(LineType.SigResult))
            {
                emptyLinesCount++;
            }

            if (Settings.Table.MaxEmptyRows > 0 &&
                Settings.Table.MaxEmptyRows < emptyLinesCount &&
                line.IsEmpty)
            {
                index++;
                continue;
            }

            if (line.TestLineType(LineType.Base))
            {
                baseRows.Add(_row);
            }

            if (line.TestLineType(LineType.InnerSpec))
            {
                innerTextRows.Add(_row);
            }

            if (!line.IsEmpty && !line.TestLineType(LineType.Base))
            {
                notEmptyRows.Add(_row);
            }

            if (!WriteTableLine(line))
            {
                ExecuteTableSigResults();
                return false;
            }

            // 写入关联的显著性行
            if (line.RelativeSignificantTestLine != null)
            {
                // 不合并显著性行时，写在下一行
                if (!Settings.Sig.Merge)
                {
                    _row++;
                    sigResultRows.Add(_row);
                }

                WriteTableSigLine(line.RelativeSignificantTestLine, sigMergedCells);
            }

            _row++;
            index++;
        }

        if (_row - tableBodyStartRow > 1 && !Settings.Table.SideColorIgnoreEmpty)
        {
            Worksheet.GetRange(tableBodyStartRow, 1, _row - 1, 1).SetStyle(Settings.Table.SideStyleOptions);

            if (table.Columns > 1)
            {
                Worksheet.GetRange(tableBodyStartRow, 2, _row - 1, table.Columns)
                    .SetStyle(Settings.Table.DataStyleOptions);
            }
        }

        if (baseRows.Count > 0)
        {
            foreach (var row in baseRows)
            {
                Worksheet.GetRange(row, 1).SetStyle(Settings.Table.BaseLabelStyleOptions);
                if (table.Columns > 2)
                {
                    Worksheet.GetRange(row, 2, row, table.Columns)
                        .SetStyle(Settings.Table.BaseValueStyleOptions);
                }
            }
        }

        if (Settings.Table.SideColorIgnoreEmpty && notEmptyRows.Count > 0)
        {
            foreach (var row in notEmptyRows)
            {
                Worksheet.GetRange(row, 1).SetStyle(Settings.Table.SideStyleOptions);
                if (table.Columns > 2)
                {
                    Worksheet.GetRange(row, 2, row, table.Columns)
                        .SetStyle(Settings.Table.DataStyleOptions);
                }
            }
        }

        if (innerTextRows.Count > 0)
        {
            foreach (var row in innerTextRows)
            {
                Worksheet.GetRange(row, 1).SetStyle(Settings.Table.InnerTextStyleOptions);
            }
        }

        if (sigResultRows.Count > 0 && table.Columns > 1)
        {
            foreach (var row in sigResultRows)
            {
                Worksheet.GetRange(row, 2, row, table.Columns).SetStyle(Settings.Sig.SigResultStyleOptions);
            }
        }

        if (sigMergedCells.Count > 0 && table.Columns > 1)
        {
            foreach (var cell in sigMergedCells)
            {
                cell.SetStyle(Settings.Sig.SigResultStyleOptions);
            }
        }

        ExecuteTableSigResults();

        return true;
    }

    private bool FinishTable(ITable table, int index, int tableIndex)
    {
        if (index >= table.Count)
        {
            return true;
        }

        while (index < table.Count)
        {
            var line = table[index];

            if (!line.IsEmpty)
            {
                if (Settings.Table.RemoveTailSpec && line.TestLineType(LineType.TailSpec))
                {
                    return true;
                }

                if (!WriteTableLine(line))
                {
                    return false;
                }

                if (Settings.Table.CorrectTableNumber &&
                    !string.IsNullOrEmpty(Settings.Table.TableNumberFeature) &&
                    !string.IsNullOrEmpty(Settings.Table.TableNumberFormat) &&
                    line.Count > 0 && line[0].Text.StartsWith(Settings.Table.TableNumberFeature))
                {
                    Worksheet.GetRange(_row, 1).Value = string.Format(Settings.Table.TableNumberFormat,
                        tableIndex, _reader.TablesCount);
                }
            }

            ++_row;
            ++index;
        }

        return true;
    }

    private bool WriteTable(ITable? table, int tableIndex)
    {
        if (table == null)
        {
            return false;
        }

        if (Settings.Table.IntervalRows > 0)
        {
            _row += Settings.Table.IntervalRows;
        }
        else
        {
            _row += table.StartLineIndex - table.LastEndIndex - 1;
        }

        if (!StartTable(table, out var index, out var sectionText))
        {
            return false;
        }

        var headerStartRow = _row;

        if (!FillHeader(table, ref index))
        {
            return false;
        }

        if (!FillTitle(table, sectionText, headerStartRow))
        {
            return false;
        }

        return FillTable(table, ref index) && FinishTable(table, index, tableIndex);
    }

    private void FillWorksheet()
    {
        FillWorksheet(CancellationToken.None);
    }


    private string FormatProgressText(ITable table, double progress)
    {
        return progress == 100
            ? string.Format(TableFormatterMessages.FinishFormattingWorksheet, _worksheet?.Name)
            : string.Format(TableFormatterMessages.CurrentFormattingTable, table.TitleCell.Text);
    }


    private void ReorderTables()
    {
        if (Settings.TablesReordering.Count == 0)
        {
            return;
        }

        foreach (var pair in Settings.TablesReordering)
        {
            var sourceTable = pair.Key;
            var orderTables = pair.Value;
            var sourceName = pair.Key;

            if (!sourceTable.StartsWith(Settings.Table.TitleFeature))
            {
                sourceName = Settings.Table.TitleFeature + sourceName;
            }

            var tables = _reader.FindTables(sourceName);

            if (tables.Length == 0)
            {
                ReportService?.Warn(TableFormatterMessages.TableIsNotFound, sourceTable);
                continue;
            }

            if (tables.Length > 1)
            {
                ReportService?.Warn(TableFormatterMessages.MultiTablesWithSameName, $"已跳过'{sourceTable}'的排序任务。");
                continue;
            }

            // 依次查找排序表格
            foreach (var orderTable in orderTables)
            {
                var searchName = orderTable;

                if (!searchName.StartsWith(Settings.Table.TitleFeature))
                {
                    searchName = Settings.Table.TitleFeature + searchName;
                }

                _reader.FindTables(searchName).ForEach(t => t.OrderBy(tables[0]));
            }
        }

        ReportService?.Log(TableFormatterMessages.TableReorderFinished);
    }


    private void FillWorksheet(CancellationToken cancellationToken)
    {
        if (_reader.FileSpecLine != null)
        {
            _row = _reader.FileSpecLine.OriginalIndex + 1;
            var specCell = Worksheet.GetRange(Math.Max(2, _row), 1);
            specCell.Value = _reader.FileSpecLine[0].Value;
        }
        else if (_reader.FirstOriginalTable != null)
        {
            _row = _reader.FirstOriginalTable.StartLineIndex + 1;
        }
        else
        {
            _row = 8;
        }

        var i = 0;
        foreach (var table in ReportService?.WithProgress(_reader, _reader.TablesCount, FormatProgressText) ??
                              (IEnumerable<ITable>)_reader)
        {
            if (cancellationToken.IsCancellationRequested)
            {
                return;
            }

            if (!WriteTable(table, i + 1))
            {
                break;
            }

            i++;
        }

        if (Settings.Table.FixedColumnWidth == 0)
        {
            Worksheet.AutoFitEntireColumns();
        }
        else
        {
            Worksheet.SetColumnWidth(Settings.Table.FixedColumnWidth);
        }

        Worksheet.SetColumnWidth(1,
            Settings.Table.FirstColumnWidth > 0
                ? Settings.Table.FirstColumnWidth
                : TableFormatterDefaultValues.TableFirstColumnWidth);

        Worksheet.SetColumnAlignment(ExcelHAlign.Center, ExcelVAlign.Center);
        Worksheet.SetColumnAlignment(1, ExcelHAlign.Left, ExcelVAlign.Center);
    }

    private void Finish(string outputFileName, bool success)
    {
        if (success)
        {
            ReportService?.Directory(Path.GetDirectoryName(outputFileName) ?? "", "输出结果目标文件夹");
            ReportService?.Directory(outputFileName, "输出结果目标文件");
        }

        _menuBuilder?.Finish();
        _menuBuilder = null;
        _workbook?.Save();
        _workbook?.Close();
        _reader.Clear();
        _worksheet = null;
        _workbook = null;

        GC.Collect();
    }


    private struct SigComparisonResult
    {
        public int Row;
        public int Column;
        public SigComparisonMarkType MarkType;
        public ExcelStyleColor Color;
        public ExcelStyleColor AnotherColor;
        public SigComparison Comparison;
    }
}